package com.ldj.hibernate;

import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Query;

import com.ldj.struts.form.EmployerContractInfoForm;

public class EmployerContractInfoDAO extends BaseHibernateDAO {
	public EmployerContractInfoDAO(HttpServletRequest request, HttpServletResponse response) {
		super(request, response);
		// TODO Auto-generated constructor stub
	}

	private static final Log log = LogFactory.getLog(EmployerContractInfoDAO.class);
	
	public List findAllPage(String eid, EmployerContractInfoForm eForm, String placeOgan) {
		try {
			String queryString = "from EmployerContractInfo where empConDelOper='0'";
			if(eid != null && !"".equals(eid)){
				queryString +=" and employerInfo='" + eid + "'";
			}
			if(eForm.getEmpConEmpName() != null && !"".equals(eForm.getEmpConEmpName())){
				queryString += " and empConEmpName like '%" + eForm.getEmpConEmpName() + "%'";
			}
			if(eForm.getEmpConEmpSex() != null && !"".equals(eForm.getEmpConEmpSex())){
				queryString += " and empConEmpSex='" + eForm.getEmpConEmpSex()+ "'";
			}
			if(eForm.getEmpConIdCardNum() != null && !"".equals(eForm.getEmpConIdCardNum())){
				queryString += " and empConIdCardNum like '%" + eForm.getEmpConIdCardNum()+ "%'";
			}
			if(eForm.getEmpConEducationLevel() != null && !"".equals(eForm.getEmpConEducationLevel())){
				queryString += " and empConEducationLevel='" + eForm.getEmpConEducationLevel()+ "'";
			}
			if(eForm.getEmpConContractTime() != null && !"".equals(eForm.getEmpConContractTime())){
				queryString +=" and empConContractTime='"+eForm.getEmpConContractTime()+"'";
			}
			if(placeOgan != null && !"".equals(placeOgan)){
				queryString +=" and employerInfo.empInfoPlaceOrgan like '"+placeOgan+"%'";
			}
			queryString += " order by empConId";
			Query queryObject = getSession().createQuery(queryString);
			this.setPageSize(10);
			return this.jlist(queryObject);
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}
	
	
	
	public List findMyPage(String scorp, EmployerContractInfoForm eForm) {
		try {
			String queryString = "from EmployerContractInfo where empConDelOper='0' and employerInfo.empInfoPlaceOrgan in ('" + scorp + "')";
			if(eForm.getEmpConEmpName() != null && !"".equals(eForm.getEmpConEmpName())){
				queryString += " and empConEmpName like '%" + eForm.getEmpConEmpName() + "%'";
			}
			if(eForm.getEmpConEmpSex() != null && !"".equals(eForm.getEmpConEmpSex())){
				queryString += " and empConEmpSex='" + eForm.getEmpConEmpSex()+ "'";
			}
			if(eForm.getEmpConIdCardNum() != null && !"".equals(eForm.getEmpConIdCardNum())){
				queryString += " and empConIdCardNum like '%" + eForm.getEmpConIdCardNum()+ "%'";
			}
			if(eForm.getEmpConEducationLevel() != null && !"".equals(eForm.getEmpConEducationLevel())){
				queryString += " and empConEducationLevel='" + eForm.getEmpConEducationLevel()+ "'";
			}
			queryString += " order by empConId";
			Query queryObject = getSession().createQuery(queryString);
			this.setPageSize(13);
			return this.jlist(queryObject);
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}
	
	public EmployerContractInfo findById(java.lang.String id) {
		log.debug("getting EmployerContractInfo instance with id: " + id);
		try {
			EmployerContractInfo instance = (EmployerContractInfo) getSession().get(
					"com.ldj.hibernate.EmployerContractInfo", id);
			return instance;
		} catch (RuntimeException re) {
			log.error("get failed", re);
			throw re;
		}
	}
	
	
	public EmployerContractInfo find(java.lang.String id) {
		try {
			EmployerContractInfo e = null;
			String queryString = "from EmployerContractInfo where empConDelOper='0' and employerInfo='" + id + "'";
			Query queryObject = getSession().createQuery(queryString);
			List list = queryObject.list();
			if(list != null && list.size() > 0){
				e = (EmployerContractInfo)list.get(0);
			}
			return e;
		} catch (RuntimeException re) {
			log.error("get failed", re);
			throw re;
		}
	}
	
	public int findByUnitId(java.lang.String id) {
		try {	
			String	countsql="select count(*) from EmployerContractInfo where  employerInfo ='"+id+"' and EMP_CON_DEL_OPER='0' and EMP_CON_STATE ='0' and EMP_CON_CONTRACT_END_TIME >= SYSDATE and EMP_CON_CONTRACT_END_TIME <= add_months(SYSDATE,1)";
			    Query q = getSession().createQuery(countsql);
			    List cc = q.list();
			    Integer a = (Integer) cc.get(0);
			    return a.intValue();
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}
	
	
	public List findUpComContrByUnitId(java.lang.String id, EmployerContractInfoForm eForm) {
		try {	
			String	queryString="from EmployerContractInfo where  employerInfo ='"+id+"' and EMP_CON_DEL_OPER='0' and EMP_CON_STATE ='0' and EMP_CON_CONTRACT_END_TIME >= SYSDATE and EMP_CON_CONTRACT_END_TIME <= add_months(SYSDATE,1)";
			if(eForm.getEmpConEmpName() != null && !"".equals(eForm.getEmpConEmpName())){
				queryString += " and empConEmpName like '%" + eForm.getEmpConEmpName() + "%'";
			}
			if(eForm.getEmpConEmpSex() != null && !"".equals(eForm.getEmpConEmpSex())){
				queryString += " and empConEmpSex='" + eForm.getEmpConEmpSex()+ "'";
			}
			if(eForm.getEmpConIdCardNum() != null && !"".equals(eForm.getEmpConIdCardNum())){
				queryString += " and empConIdCardNum like '%" + eForm.getEmpConIdCardNum()+ "%'";
			}
			if(eForm.getEmpConEducationLevel() != null && !"".equals(eForm.getEmpConEducationLevel())){
				queryString += " and empConEducationLevel='" + eForm.getEmpConEducationLevel()+ "'";
			}
			queryString += "order by empConId";
			Query queryObject = getSession().createQuery(queryString);
			this.setPageSize(10);
			return this.jlist(queryObject);
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}
	
	
	public void save(EmployerContractInfo transientInstance) {
		log.debug("saving EmployerContractInfo instance");
		try {
			getSession().save(transientInstance);
			log.debug("save successful");
		} catch (RuntimeException re) {
			log.error("save failed", re);
			throw re;
		}
	}
	
	public EmployerContractInfo merge(EmployerContractInfo detachedInstance) {
		try {
			EmployerContractInfo result = (EmployerContractInfo) getSession().merge(detachedInstance);
			log.debug("merge successful");
			return result;
		} catch (RuntimeException re) {
			log.error("merge failed", re);
			throw re;
		}
	}
	
	public void delete(EmployerContractInfo persistentInstance) {
		log.debug("deleting EmployerInfo instance");
		try {
			getSession().delete(persistentInstance);
			log.debug("delete successful");
		} catch (RuntimeException re) {
			log.error("delete failed", re);
			throw re;
		}
	}
	
	
	public List findFromCode(String areaCode) {
		try {
			String queryString = "select";
			queryString += " dd.dataDictionaryName,";
			queryString += " (select count(*) from EmployerInfo ei where ei.empInfoUnitType=dd.dataDictionaryCode and ei.empInfoDelOper='0' and ei.empInfoPlaceOrgan like '" + areaCode + "%' ) as empTotalCount,";
			queryString += " (select sum(empInfoAllWorkNum) from EmployerInfo ei where ei.empInfoUnitType=dd.dataDictionaryCode and ei.empInfoDelOper='0' and ei.empInfoPlaceOrgan like '" + areaCode + "%') as empWorkerTotalCount,";
			queryString += " (select count(empInfoIfContract) from EmployerInfo ei where ei.empInfoUnitType=dd.dataDictionaryCode and ei.empInfoDelOper='0' and ei.empInfoIfContract='0' and ei.empInfoPlaceOrgan like '" + areaCode + "%') as empContractTotalCount,";
			queryString += " (select sum(empInfoContractedNum) from EmployerInfo ei where ei.empInfoUnitType=dd.dataDictionaryCode and ei.empInfoDelOper='0' and ei.empInfoPlaceOrgan like '" + areaCode + "%') as empWorkerContractTotalCount";
			queryString += " from";
			queryString += " DataDictionary dd";
			queryString += " where dd.dataDictionaryCode like '001%' and dd.dataDictionaryCode !='001'" +
					" order by dd.dataDictionaryCode";
			Query queryObject = getSession().createQuery(queryString);
			return queryObject.list();
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}
	
}